﻿using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using DevExpress.XtraReports.UI;
using DevExpress.XtraCharts;
using ERP_RORZE_SYSTEM;
using System.Data;
using System.Windows.Forms;

namespace ERP_RORZE_DLL.Assembly
{
    public partial class rpt_F5DirectIndirect : DevExpress.XtraReports.UI.XtraReport
    {
        public string year;
        private DataTable TotalTable;
        private DataTable ShowTable;
        private DataTable DirectTable;
        private DataTable IndirectTable;
        private DataTable TurnOverTable;

        private DataTable CtrWorkIDShow;
        private DataTable CtrWorkIDRate;
        private DataTable CtrWorkIDChart;

        public rpt_F5DirectIndirect()
        {
            InitializeComponent();
            this.year = null;
            this.TotalTable = new DataTable();
            this.ShowTable = new DataTable();
            this.DirectTable = new DataTable();
            this.DirectTable.Columns.Add("Month", typeof(string));
            this.DirectTable.Columns.Add("Value", typeof(double));
            this.IndirectTable = new DataTable();
            this.IndirectTable.Columns.Add("Month", typeof(string));
            this.IndirectTable.Columns.Add("Value", typeof(double));
            this.TurnOverTable = new DataTable();
            this.TurnOverTable.Columns.Add("Month", typeof(string));
            this.TurnOverTable.Columns.Add("Value", typeof(double));

            this.CtrWorkIDRate = new DataTable();
            this.CtrWorkIDChart = new DataTable();
            this.CtrWorkIDShow = new DataTable();
        }

        private void BindData()
        {
            yearLabel.Text = this.year;
            janLabel.Text = "Jan-" + this.year.Substring(2, 2);
            febLabel.Text = "Feb-" + this.year.Substring(2, 2);
            marLabel.Text = "Mar-" + this.year.Substring(2, 2);
            aprLabel.Text = "Apr-" + this.year.Substring(2, 2);
            mayLabel.Text = "May-" + this.year.Substring(2, 2);
            junLabel.Text = "Jun-" + this.year.Substring(2, 2);
            julLabel.Text = "Jul-" + this.year.Substring(2, 2);
            augLabel.Text = "Aug-" + this.year.Substring(2, 2);
            sepLabel.Text = "Sep-" + this.year.Substring(2, 2);
            octLabel.Text = "Oct-" + this.year.Substring(2, 2);
            novLabel.Text = "Nov-" + this.year.Substring(2, 2);
            decLabel.Text = "Dec-" + this.year.Substring(2, 2);
            
            itemDetail.DataBindings.Add("Text", this.Controller.DataSource, "Items");
            yearWork.DataBindings.Add("Text", this.Controller.DataSource, this.year);
            janWork.DataBindings.Add("Text", this.Controller.DataSource, "Jan-" + this.year.Substring(2, 2));
            febWork.DataBindings.Add("Text", this.Controller.DataSource, "Feb-" + this.year.Substring(2, 2));
            marWork.DataBindings.Add("Text", this.Controller.DataSource, "Mar-" + this.year.Substring(2, 2));
            aprWork.DataBindings.Add("Text", this.Controller.DataSource, "Apr-" + this.year.Substring(2, 2));
            mayWork.DataBindings.Add("Text", this.Controller.DataSource, "May-" + this.year.Substring(2, 2));
            junWork.DataBindings.Add("Text", this.Controller.DataSource, "Jun-" + this.year.Substring(2, 2));
            julWork.DataBindings.Add("Text", this.Controller.DataSource, "Jul-" + this.year.Substring(2, 2));
            augWork.DataBindings.Add("Text", this.Controller.DataSource, "Aug-" + this.year.Substring(2, 2));
            sepWork.DataBindings.Add("Text", this.Controller.DataSource, "Sep-" + this.year.Substring(2, 2));
            octWork.DataBindings.Add("Text", this.Controller.DataSource, "Oct-" + this.year.Substring(2, 2));
            novWork.DataBindings.Add("Text", this.Controller.DataSource, "Nov-" + this.year.Substring(2, 2));
            decWork.DataBindings.Add("Text", this.Controller.DataSource, "Dec-" + this.year.Substring(2, 2));

            if (DirectTable.Rows.Count > 0)
            {
                DirectTable.Rows[0].Delete();
            }
            // Specify data members to bind the series.
            controlerTurnOverChart.Series["Direct/working"].DataSource = DirectTable;            
            controlerTurnOverChart.Series["Direct/working"].ArgumentScaleType = ScaleType.Auto;
            controlerTurnOverChart.Series["Direct/working"].ArgumentDataMember = "Month";
            controlerTurnOverChart.Series["Direct/working"].ValueScaleType = ScaleType.Numerical;
            controlerTurnOverChart.Series["Direct/working"].ValueDataMembers.AddRange(new string[] { "Value" });

            if (IndirectTable.Rows.Count > 0)
            {
                IndirectTable.Rows[0].Delete();
            }
            // Specify data members to bind the series.
            controlerTurnOverChart.Series["Indirect/working"].DataSource = IndirectTable;
            controlerTurnOverChart.Series["Indirect/working"].ArgumentScaleType = ScaleType.Auto;
            controlerTurnOverChart.Series["Indirect/working"].ArgumentDataMember = "Month";
            controlerTurnOverChart.Series["Indirect/working"].ValueScaleType = ScaleType.Numerical;
            controlerTurnOverChart.Series["Indirect/working"].ValueDataMembers.AddRange(new string[] { "Value" });

            // Specify data members to bind the series.
            controlerTurnOverChart.Series["Turnover"].DataSource = TurnOverTable;
            controlerTurnOverChart.Series["Turnover"].ArgumentScaleType = ScaleType.Auto;
            controlerTurnOverChart.Series["Turnover"].ArgumentDataMember = "Month";
            controlerTurnOverChart.Series["Turnover"].ValueScaleType = ScaleType.Numerical;
            controlerTurnOverChart.Series["Turnover"].ValueDataMembers.AddRange(new string[] { "Value" });

            // Indirect Rate for Controller
            ctrTotalLabel.Text = "Total " + this.year;
            ctrJanLabel.Text = "Jan-" + this.year.Substring(2, 2);
            ctrFebLabel.Text = "Feb-" + this.year.Substring(2, 2);
            ctrMarLabel.Text = "Mar-" + this.year.Substring(2, 2);
            ctrAprLabel.Text = "Apr-" + this.year.Substring(2, 2);
            ctrMayLabel.Text = "May-" + this.year.Substring(2, 2);
            ctrJunLabel.Text = "Jun-" + this.year.Substring(2, 2);
            ctrJulLabel.Text = "Jul-" + this.year.Substring(2, 2);
            ctrAugLabel.Text = "Aug-" + this.year.Substring(2, 2);
            ctrSepLabel.Text = "Sep-" + this.year.Substring(2, 2);
            ctrOctLabel.Text = "Oct-" + this.year.Substring(2, 2);
            ctrNovLabel.Text = "Nov-" + this.year.Substring(2, 2);
            ctrDecLabel.Text = "Dec-" + this.year.Substring(2, 2);

            for (int i = 1; i <= 12; i++)
            {
                XRLabel TotalCtrTimeLabel = (XRLabel)this.FindControl("TotalCtrTime" + i.ToString(), false);
                TotalCtrTimeLabel.Text = ShowTable.Rows[0][i+1].ToString();

                XRLabel DirTotalCtrTimeLabel = (XRLabel)this.FindControl("DirTotalCtrTime" + i.ToString(), false);
                DirTotalCtrTimeLabel.Text = ShowTable.Rows[1][i + 1].ToString();

                XRLabel InDirTotalCtrTimeLabel = (XRLabel)this.FindControl("InDirTotalCtrTime" + i.ToString(), false);
                InDirTotalCtrTimeLabel.Text = ShowTable.Rows[2][i + 1].ToString();
            }

            TotalCtrTime.Text = ShowTable.Rows[0][1].ToString();
            DirTotalCtrTime.Text = ShowTable.Rows[1][1].ToString();
            InDirTotalCtrTime.Text = ShowTable.Rows[2][1].ToString();

            numberCol.DataBindings.Add("Text", this.ControllerWorkID.DataSource, "No.");
            codeCol.DataBindings.Add("Text", this.ControllerWorkID.DataSource, "TimeWorkID");
            itemCol.DataBindings.Add("Text", this.ControllerWorkID.DataSource, "TimeWorkName");
            janCol.DataBindings.Add("Text", this.ControllerWorkID.DataSource, "Jan-" + this.year.Substring(2, 2));
            febCol.DataBindings.Add("Text", this.ControllerWorkID.DataSource, "Feb-" + this.year.Substring(2, 2));
            marCol.DataBindings.Add("Text", this.ControllerWorkID.DataSource, "Mar-" + this.year.Substring(2, 2));
            aprCol.DataBindings.Add("Text", this.ControllerWorkID.DataSource, "Apr-" + this.year.Substring(2, 2));
            mayCol.DataBindings.Add("Text", this.ControllerWorkID.DataSource, "May-" + this.year.Substring(2, 2));
            junCol.DataBindings.Add("Text", this.ControllerWorkID.DataSource, "Jun-" + this.year.Substring(2, 2));
            julCol.DataBindings.Add("Text", this.ControllerWorkID.DataSource, "Jul-" + this.year.Substring(2, 2));
            augCol.DataBindings.Add("Text", this.ControllerWorkID.DataSource, "Aug-" + this.year.Substring(2, 2));
            sepCol.DataBindings.Add("Text", this.ControllerWorkID.DataSource, "Sep-" + this.year.Substring(2, 2));
            octCol.DataBindings.Add("Text", this.ControllerWorkID.DataSource, "Oct-" + this.year.Substring(2, 2));
            novCol.DataBindings.Add("Text", this.ControllerWorkID.DataSource, "Nov-" + this.year.Substring(2, 2));
            decCol.DataBindings.Add("Text", this.ControllerWorkID.DataSource, "Dec-" + this.year.Substring(2, 2));
            yearCol.DataBindings.Add("Text", this.ControllerWorkID.DataSource, this.year);
            percentCol.DataBindings.Add("Text", this.ControllerWorkID.DataSource, "Percent");

            for (int i = 1; i < this.CtrWorkIDChart.Columns.Count; i++)
            {
                this.controllerWorkIDChart.Series.Add(this.CtrWorkIDChart.Columns[i].ColumnName, ViewType.StackedBar);
                // Specify data members to bind the series.
                this.controllerWorkIDChart.Series[this.CtrWorkIDChart.Columns[i].ColumnName].DataSource = this.CtrWorkIDChart;
                this.controllerWorkIDChart.Series[this.CtrWorkIDChart.Columns[i].ColumnName].ArgumentScaleType = ScaleType.Auto;
                this.controllerWorkIDChart.Series[this.CtrWorkIDChart.Columns[i].ColumnName].ArgumentDataMember = "Month";
                this.controllerWorkIDChart.Series[this.CtrWorkIDChart.Columns[i].ColumnName].ValueScaleType = ScaleType.Numerical;
                this.controllerWorkIDChart.Series[this.CtrWorkIDChart.Columns[i].ColumnName].ValueDataMembers.AddRange(new string[] { this.CtrWorkIDChart.Columns[i].ColumnName });
                this.controllerWorkIDChart.Series[this.CtrWorkIDChart.Columns[i].ColumnName].LabelsVisibility = DevExpress.Utils.DefaultBoolean.False;
            }

            this.controllerWorkIDRateChart.Series["ControllerWorkIDRate"].DataSource = this.CtrWorkIDRate;
            this.controllerWorkIDRateChart.Series["ControllerWorkIDRate"].ArgumentScaleType = ScaleType.Auto;
            this.controllerWorkIDRateChart.Series["ControllerWorkIDRate"].ArgumentDataMember = "Items";
            this.controllerWorkIDRateChart.Series["ControllerWorkIDRate"].ValueScaleType = ScaleType.Numerical;
            this.controllerWorkIDRateChart.Series["ControllerWorkIDRate"].ValueDataMembers.AddRange(new string[] { "Percent" });
            this.controllerWorkIDRateChart.Series["ControllerWorkIDRate"].LegendText = this.controllerWorkIDRateChart.Series["ControllerWorkIDRate"].ArgumentDataMember;            
            this.controllerWorkIDRateChart.Series["ControllerWorkIDRate"].LegendPointOptions.PointView = PointView.Argument;
        }

        public void LoadData()
        {
            DataSet controllerDataSet = new DataSet();
            DataSet ctrWorkIDDataSet = new DataSet();
            DataSet ctrTurnOverDataSet = new DataSet();
            INISettings mySettings = new INISettings();
            SQLManagement mySQLManagement = new SQLManagement();
            mySQLManagement.SQLConnectionString = mySettings.SQLConnectionString;

            // Get data for Controller Report
            string sql = "SELECT 'Total working time' AS [Items], SUM([AssemblyTime]) AS [" + this.year + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 1 THEN [AssemblyTime] ELSE 0 END) AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 2 THEN [AssemblyTime] ELSE 0 END) AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 3 THEN [AssemblyTime] ELSE 0 END) AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 4 THEN [AssemblyTime] ELSE 0 END) AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 5 THEN [AssemblyTime] ELSE 0 END) AS [May-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 6 THEN [AssemblyTime] ELSE 0 END) AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 7 THEN [AssemblyTime] ELSE 0 END) AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 8 THEN [AssemblyTime] ELSE 0 END) AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 9 THEN [AssemblyTime] ELSE 0 END) AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 10 THEN [AssemblyTime] ELSE 0 END) AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 11 THEN [AssemblyTime] ELSE 0 END) AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 12 THEN [AssemblyTime] ELSE 0 END) AS [Dec-" + this.year.Substring(2, 2) + "]"
                + " FROM [231_TimeInput]"
                + " WHERE (F5 = 1) AND (DATEPART(year, [Date]) = " + this.year + ")"
                + " AND TimePlanNo NOT IN ('A000000', 'M000000')"
                + " AND ((TimeWorkID LIKE 'M%') OR (TimeWorkID IN ('3631', '8YZ1', 'OOO1', 'OE01', '8SZ1', '8ZQ1')))"
                + " UNION ALL"
                + " SELECT 'Total direct time' AS [Items], SUM([AssemblyTime]) AS [" + this.year + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 1 THEN [AssemblyTime] ELSE 0 END) AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 2 THEN [AssemblyTime] ELSE 0 END) AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 3 THEN [AssemblyTime] ELSE 0 END) AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 4 THEN [AssemblyTime] ELSE 0 END) AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 5 THEN [AssemblyTime] ELSE 0 END) AS [May-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 6 THEN [AssemblyTime] ELSE 0 END) AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 7 THEN [AssemblyTime] ELSE 0 END) AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 8 THEN [AssemblyTime] ELSE 0 END) AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 9 THEN [AssemblyTime] ELSE 0 END) AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 10 THEN [AssemblyTime] ELSE 0 END) AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 11 THEN [AssemblyTime] ELSE 0 END) AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 12 THEN [AssemblyTime] ELSE 0 END) AS [Dec-" + this.year.Substring(2, 2) + "]"
                + " FROM [231_TimeInput]"
                + " WHERE (F5 = 1) AND (DATEPART(year, [Date]) = " + this.year + ")"
                + " AND TimePlanNo NOT IN ('A000000', 'M000000')"
                + " AND ((TimeWorkID LIKE 'M%') OR (TimeWorkID IN ('3631', '8YZ1', 'OOO1', 'OE01', '8SZ1', '8ZQ1')))"
                + " AND (SUBSTRING(TimePlanNo, 1, 3) <> 'Z20')"
                + " UNION ALL"
                + " SELECT 'Total indirect time' AS [Items], SUM([AssemblyTime]) AS [" + this.year + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 1 THEN [AssemblyTime] ELSE 0 END) AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 2 THEN [AssemblyTime] ELSE 0 END) AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 3 THEN [AssemblyTime] ELSE 0 END) AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 4 THEN [AssemblyTime] ELSE 0 END) AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 5 THEN [AssemblyTime] ELSE 0 END) AS [May-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 6 THEN [AssemblyTime] ELSE 0 END) AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 7 THEN [AssemblyTime] ELSE 0 END) AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 8 THEN [AssemblyTime] ELSE 0 END) AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 9 THEN [AssemblyTime] ELSE 0 END) AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 10 THEN [AssemblyTime] ELSE 0 END) AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 11 THEN [AssemblyTime] ELSE 0 END) AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, [Date]) = 12 THEN [AssemblyTime] ELSE 0 END) AS [Dec-" + this.year.Substring(2, 2) + "]"
                + " FROM [231_TimeInput]"
                + " WHERE (F5 = 1) AND (DATEPART(year, [Date]) = " + this.year + ")"
                + " AND TimePlanNo NOT IN ('A000000', 'M000000')"
                + " AND ((TimeWorkID LIKE 'M%') OR (TimeWorkID IN ('3631', '8YZ1', 'OOO1', 'OE01', '8SZ1', '8ZQ1')))"
                + " AND (SUBSTRING(TimePlanNo, 1, 3) = 'Z20')";
            string sql_ctrlWorkID = "SELECT row_number() OVER (ORDER BY t1.[TimeWorkID], t2.TimeWorkName) [No.],"
                + " t1.[TimeWorkID], t2.TimeWorkName,"
                + " SUM(CASE WHEN DATEPART(month, t1.[Date]) = 1 THEN t1.[AssemblyTime] ELSE 0 END) AS [Jan-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t1.[Date]) = 2 THEN t1.[AssemblyTime] ELSE 0 END) AS [Feb-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t1.[Date]) = 3 THEN t1.[AssemblyTime] ELSE 0 END) AS [Mar-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t1.[Date]) = 4 THEN t1.[AssemblyTime] ELSE 0 END) AS [Apr-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t1.[Date]) = 5 THEN t1.[AssemblyTime] ELSE 0 END) AS [May-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t1.[Date]) = 6 THEN t1.[AssemblyTime] ELSE 0 END) AS [Jun-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t1.[Date]) = 7 THEN t1.[AssemblyTime] ELSE 0 END) AS [Jul-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t1.[Date]) = 8 THEN t1.[AssemblyTime] ELSE 0 END) AS [Aug-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t1.[Date]) = 9 THEN t1.[AssemblyTime] ELSE 0 END) AS [Sep-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t1.[Date]) = 10 THEN t1.[AssemblyTime] ELSE 0 END) AS [Oct-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t1.[Date]) = 11 THEN t1.[AssemblyTime] ELSE 0 END) AS [Nov-" + this.year.Substring(2, 2) + "],"
                + " SUM(CASE WHEN DATEPART(month, t1.[Date]) = 12 THEN t1.[AssemblyTime] ELSE 0 END) AS [Dec-" + this.year.Substring(2, 2) + "],"
                + " SUM(t1.[AssemblyTime]) AS [" + this.year + "]"
                + " FROM [231_TimeInput] AS t1 LEFT JOIN"
                + " [231_TimeWork] AS t2 ON t1.TimeWorkID = t2.TimeWorkID"
                + " WHERE (t1.F5 = 1) AND (DATEPART(year, t1.[Date]) = " + this.year + ")"
                + " AND ((t1.TimeWorkID LIKE 'M%') OR (t1.TimeWorkID IN ('3631', '8YZ1', 'OOO1', 'OE01', '8SZ1', '8ZQ1')))"
                + " AND (SUBSTRING(t1.TimePlanNo, 1, 3) = 'Z20')"
                + " GROUP BY t1.[TimeWorkID], t2.TimeWorkName";

            string sql_TurnOver = "SELECT"
                + " CASE WHEN SUBSTRING(MonthOfYear, 5, 2) = '01' THEN 'Jan-' + SUBSTRING(MonthOfYear, 3, 2)"
                + " WHEN SUBSTRING(MonthOfYear, 5, 2) = '02' THEN 'Feb-' + SUBSTRING(MonthOfYear, 3, 2)"
                + " WHEN SUBSTRING(MonthOfYear, 5, 2) = '03' THEN 'Mar-' + SUBSTRING(MonthOfYear, 3, 2)"
                + " WHEN SUBSTRING(MonthOfYear, 5, 2) = '04' THEN 'Apr-' + SUBSTRING(MonthOfYear, 3, 2)"
                + " WHEN SUBSTRING(MonthOfYear, 5, 2) = '05' THEN 'May-' + SUBSTRING(MonthOfYear, 3, 2)"
                + " WHEN SUBSTRING(MonthOfYear, 5, 2) = '06' THEN 'Jun-' + SUBSTRING(MonthOfYear, 3, 2)"
                + " WHEN SUBSTRING(MonthOfYear, 5, 2) = '07' THEN 'Jul-' + SUBSTRING(MonthOfYear, 3, 2)"
                + " WHEN SUBSTRING(MonthOfYear, 5, 2) = '08' THEN 'Aug-' + SUBSTRING(MonthOfYear, 3, 2)"
                + " WHEN SUBSTRING(MonthOfYear, 5, 2) = '09' THEN 'Sep-' + SUBSTRING(MonthOfYear, 3, 2)"
                + " WHEN SUBSTRING(MonthOfYear, 5, 2) = '10' THEN 'Oct-' + SUBSTRING(MonthOfYear, 3, 2)"
                + " WHEN SUBSTRING(MonthOfYear, 5, 2) = '11' THEN 'Nov-' + SUBSTRING(MonthOfYear, 3, 2)"
                + " WHEN SUBSTRING(MonthOfYear, 5, 2) = '12' THEN 'Dec-' + SUBSTRING(MonthOfYear, 3, 2) END AS [Month],"
                + " ISNULL(TurnOver, 0) AS TurnOver"
                + " FROM [231_TurnOver]"
                + " WHERE SUBSTRING(MonthOfYear, 1, 4) = '" + this.year + "'";

            controllerDataSet = mySQLManagement.SQL_Query(sql);
            if (controllerDataSet != null)
            {                
                try
                {
                    this.TotalTable = controllerDataSet.Tables[0].Copy();
                    this.ShowTable = this.TotalTable.Clone();
                    for (int i = 0; i < ShowTable.Columns.Count; i++)
                    {
                        ShowTable.Columns[i].DataType = typeof(string);
                    }

                    foreach (DataRow total_row in this.TotalTable.Rows)
                    {
                        ShowTable.ImportRow(total_row);
                    }

                    DataRow directRate = this.TotalTable.NewRow();
                    DataRow showdirectRate = this.ShowTable.NewRow();
                    directRate["Items"] = "Direct/Working";
                    showdirectRate["Items"] = "Direct/Working";
                    for (int i = 1; i < this.TotalTable.Columns.Count; i++)
                    {
                        DataRow add_dir = this.DirectTable.NewRow();
                        add_dir["Month"] = this.TotalTable.Columns[i].ColumnName;
                        double direct = 0;
                        double total = 0;
                        double.TryParse(this.TotalTable.Rows[1][i].ToString(), out direct);
                        double.TryParse(this.TotalTable.Rows[0][i].ToString(), out total);
                        if (total > 0)
                        {
                            directRate[i] = Math.Round((direct / total) * 100, 0);
                            add_dir["Value"] = Math.Round((direct / total) * 100, 0);
                            showdirectRate[i] = Math.Round((direct / total) * 100, 0).ToString() + "%";
                        }
                        this.DirectTable.Rows.Add(add_dir);
                    }
                    this.TotalTable.Rows.Add(directRate);
                    this.ShowTable.Rows.Add(showdirectRate);

                    DataRow indirectRate = this.TotalTable.NewRow();
                    DataRow showindirectRate = this.ShowTable.NewRow();
                    indirectRate["Items"] = "Indirect/Working";
                    showindirectRate["Items"] = "Indirect/Working";
                    for (int i = 1; i < this.TotalTable.Columns.Count; i++)
                    {
                        DataRow add_indir = this.IndirectTable.NewRow();
                        add_indir["Month"] = this.TotalTable.Columns[i].ColumnName;
                        double indirect = 0;
                        double total = 0;
                        double.TryParse(this.TotalTable.Rows[2][i].ToString(), out indirect);
                        double.TryParse(this.TotalTable.Rows[0][i].ToString(), out total);
                        if (total > 0)
                        {
                            indirectRate[i] = Math.Round((indirect / total) * 100, 0).ToString();
                            add_indir["Value"] = Math.Round((indirect / total) * 100, 0);
                            showindirectRate[i] = Math.Round((indirect / total) * 100, 0).ToString() + "%";
                        }
                        this.IndirectTable.Rows.Add(add_indir);
                    }
                    this.TotalTable.Rows.Add(indirectRate);
                    this.ShowTable.Rows.Add(showindirectRate);

                    ctrTurnOverDataSet = mySQLManagement.SQL_Query(sql_TurnOver);
                    DataRow turnover = this.TotalTable.NewRow();
                    DataRow showturnover = this.ShowTable.NewRow();
                    turnover["Items"] = "Turn over";
                    showturnover["Items"] = "Turn over";
                    foreach (DataRow turn_row in ctrTurnOverDataSet.Tables[0].Rows)
                    {
                        DataRow add_turn = this.TurnOverTable.NewRow();
                        add_turn["Month"] = turn_row["Month"].ToString();
                        add_turn["Value"] = float.Parse(turn_row["TurnOver"].ToString().Trim());
                        this.TurnOverTable.Rows.Add(add_turn);
                        turnover[turn_row["Month"].ToString().Trim()] = float.Parse(turn_row["TurnOver"].ToString().Trim());
                        showturnover[turn_row["Month"].ToString().Trim()] = turn_row["TurnOver"].ToString().Trim();
                    }
                    /*
                    for (int i = 2; i < this.TotalTable.Columns.Count; i++)
                    {
                        DataRow add_turn = this.TurnOverTable.NewRow();
                        add_turn["Month"] = this.TotalTable.Columns[i].ColumnName;
                        add_turn["Value"] = "1000000";
                        this.TurnOverTable.Rows.Add(add_turn);
                        turnover[i] = 1000000;
                        showturnover[i] = "1000000";
                    } */
                    this.TotalTable.Rows.Add(turnover);
                    this.ShowTable.Rows.Add(showturnover);

                    this.Controller.DataSource = this.ShowTable;                    
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message);
                }
            }
            else
            {
                MessageBox.Show("Cannot Load data to report");
            }
            ctrWorkIDDataSet = mySQLManagement.SQL_Query(sql_ctrlWorkID);
            if (ctrWorkIDDataSet != null)
            {
                this.CtrWorkIDShow = ctrWorkIDDataSet.Tables[0].Copy();
                this.CtrWorkIDShow.Columns.Add("Percent", typeof(string));

                this.CtrWorkIDRate.Columns.Add("Items", typeof(string));
                this.CtrWorkIDRate.Columns.Add("Percent", typeof(double));

                double totaltime = 0;
                foreach (DataRow ctrl_row in this.CtrWorkIDShow.Rows)
                {
                    totaltime += double.Parse(ctrl_row[this.year].ToString());
                }
                if (totaltime > 0)
                {
                    foreach (DataRow edit_row in this.CtrWorkIDShow.Rows)
                    {
                        double time = double.Parse(edit_row[this.year].ToString());
                        edit_row.BeginEdit();
                        edit_row["Percent"] = Math.Round((time / totaltime) * 100, 1).ToString() + "%";
                        edit_row.EndEdit();

                        DataRow rate_row = this.CtrWorkIDRate.NewRow();
                        rate_row["Items"] = edit_row["TimeWorkName"].ToString().Trim();
                        rate_row["Percent"] = Math.Round((time / totaltime) * 100, 1);
                        this.CtrWorkIDRate.Rows.Add(rate_row);
                    }
                }
                DataRow add_row = this.CtrWorkIDShow.NewRow();
                add_row[this.year] = totaltime;
                add_row["Percent"] = "100%";
                this.CtrWorkIDShow.Rows.Add(add_row);

                ControllerWorkID.DataSource = this.CtrWorkIDShow;

                this.CtrWorkIDChart.Columns.Add("Month", typeof(string));
                string year_lastdigit = this.year.Substring(2, 2);
                string[] month_label = new string[12] { "Jan-" + year_lastdigit, "Feb-" + year_lastdigit,
                "Mar-" + year_lastdigit, "Apr-" + year_lastdigit, "May-" + year_lastdigit, "Jun-" + year_lastdigit, 
                "Jul-" + year_lastdigit, "Aug-" + year_lastdigit, "Sep-" + year_lastdigit, "Oct-" + year_lastdigit, 
                "Nov-" + year_lastdigit, "Dec-" + year_lastdigit };

                for (int i = 1; i <= 12; i++)
                {
                    DataRow chart_row = this.CtrWorkIDChart.NewRow();
                    chart_row["Month"] = month_label[i - 1];
                    this.CtrWorkIDChart.Rows.Add(chart_row);
                }                
                
                string serial_name = null;
                try
                {                    
                    foreach (DataRow show_row in this.CtrWorkIDShow.Rows)
                    {
                        if (!this.CtrWorkIDChart.Columns.Contains(show_row["TimeWorkName"].ToString().Trim()))
                        {
                            if (!string.IsNullOrEmpty(show_row["TimeWorkName"].ToString().Trim()))
                            {
                                serial_name = show_row["TimeWorkName"].ToString().Trim();
                                this.CtrWorkIDChart.Columns.Add(serial_name, typeof(float));
                                for (int i = 3; i <= 14; i++)
                                {
                                    this.CtrWorkIDChart.Rows[i - 3].BeginEdit();
                                    this.CtrWorkIDChart.Rows[i - 3][serial_name] = float.Parse(show_row[i].ToString().Trim());
                                    this.CtrWorkIDChart.Rows[i - 3].EndEdit();
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.Message + ": " + serial_name);
                }
            }
            else
            {
                MessageBox.Show("Cannot Load data to report");
            }

            BindData();
        }
    }
}
